package dao.Impl;

import dao.FreelancerRepository;
import entity.Freelancer;
import utils.JDBCTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class FreelancerRepositoryImpl implements FreelancerRepository {

    /**
     * 从数据库中获取自由职业者信息表
     */
    @Override
    public  ArrayList<Freelancer> getFreelancerList(){
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from servicemanagementsystem.freelancer";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Freelancer freelancer = null;
        ArrayList<Freelancer> freelancerList = new ArrayList<>();
        try {
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Integer id = resultSet.getInt(1);
                String Name = resultSet.getString(2);
                Integer age = resultSet.getInt(3);
                String job = resultSet.getString(4);
                String addr = resultSet.getString(5);
                String phone = resultSet.getString(6);
                String wechat = resultSet.getString(7);
                String mail = resultSet.getString(8);
                freelancer = new Freelancer(id, Name, age,job,addr,phone,wechat,mail);
                freelancerList.add(freelancer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection, ps, resultSet);
        }
        return freelancerList;
    }
    /**
     * 添加自由职业者信息
     */
    @Override
    public boolean addFreelancer(Freelancer freelancer) {
        Connection connection = null;
        String sql = "insert into servicemanagementsystem.freelancer (fid, name, age, job,addr,phone,wechat,mail) " +
                "values (?,?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, freelancer.getFid());
            ps.setString(2, freelancer.getName());
            ps.setInt(3, freelancer.getAge());
            ps.setString(4, freelancer.getJob());
            ps.setString(5,freelancer.getAddr());
            ps.setString(6,freelancer.getPhone());
            ps.setString(7,freelancer.getWechat());
            ps.setString(8,freelancer.getMail());
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("error when add freelancer");
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }

    /**
     * 根据自由职业者编号查找数据库返回自由职业者信息对象
     */
    @Override
    public Freelancer findById(Integer freelancerId) {
        Connection connection = null;
        String sql = "select * from servicemanagementsystem.freelancer where fid=?";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        Freelancer freelancer = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, freelancerId);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                Integer id = resultSet.getInt(1);
                String Name = resultSet.getString(2);
                Integer age = resultSet.getInt(3);
                String job = resultSet.getString(4);
                String addr = resultSet.getString(5);
                String phone = resultSet.getString(6);
                String wechat = resultSet.getString(7);
                String mail = resultSet.getString(8);
                freelancer = new Freelancer(id, Name, age,job,addr,phone,wechat,mail);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, ps, resultSet);
        }
        return freelancer;
    }
    /**
     * 修改自由职业者信息
     */
    @Override
    public boolean updateFreelancer(Freelancer freelancer) {
        Connection connection = null;
        String sql = "update servicemanagementsystem.freelancer set name=? , age=?, job=?, addr=? ,phone=?,wechat=?,mail=? " +
                "where fid=?";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setString(1, freelancer.getName());
            ps.setInt(2, freelancer.getAge());
            ps.setString(3, freelancer.getJob());
            ps.setString(4,freelancer.getAddr());
            ps.setString(5,freelancer.getPhone());
            ps.setString(6,freelancer.getWechat());
            ps.setString(7,freelancer.getMail());
            ps.setInt(8, freelancer.getFid());
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            System.out.println("error when update freelancer");
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }

    /**
     * 根据自由职业者编号删除自由职业者信息
     */
    @Override
    public boolean deleteFreelancer(Integer freelancerId) {
        Connection connection = null;
        String sql = "delete from servicemanagementsystem.freelancer where fid = ?";
        PreparedStatement ps = null;
        try {
            connection = JDBCTools.getConnection();
            ps = connection.prepareStatement(sql);
            ps.setInt(1, freelancerId);
            int result = ps.executeUpdate();
            if (result == 1) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        } finally {
            JDBCTools.release(connection, ps, null);
        }
        return false;
    }

}
